var Db = {
	db : false,
	dbVersion : '4.5',
	databaseName : 'iPirata'
};

Db.init = function(){
	
	var currentDbVersion = Db.getVersion();
	if (currentDbVersion == false){
		Ti.API.info("Db.init: creant db");
		Ti.App.db.execute('CREATE TABLE idea ( id INTEGER, status INTEGER, data NUMERIC, titol TEXT, descripcio TEXT, esNou INTEGER, haCanviat INTEGER, vist INTEGER, ultimCanvi NUMERIC )');
		Ti.App.db.execute('CREATE TABLE solucio ( id INTEGER, idIdea INTEGER, status INTEGER, titol TEXT, descripcio TEXT,vots INTEGER, sl TEXT, esNou INTEGER, haCanviat INTEGER, vist INTEGER, ultimCanvi NUMERIC, votat NUMERIC )');
		Ti.App.db.execute('CREATE TABLE comentari ( id INTEGER, status INTEGER, idSolucio INTEGER, titol TEXT, descripcio TEXT,vots INTEGER, sl INTEGER, esNou INTEGER, haCanviat INTEGER, vist INTEGER, ultimCanvi NUMERIC )');
		Db.setVersion(Db.dbVersion);
		Ti.App.fireEvent('dbFinishedInit');
	}else{
		if (Db.dbVersion != currentDbVersion){
			Ti.API.info("Db.init: canvi de versio, recreant db");
			Db.resetDb();
			Db.init();
		}else{
			Ti.API.info("Db.init: db al dia");
			//Db.db = Ti.Database.open(Db.databaseName);
			Ti.App.fireEvent('dbFinishedInit');
		}
	}
	
};

Db.insertOrUpdateIdea = function (currentIdea){
	var isNewIdea = Db.insertIdeaOnce(
		currentIdea.id,
		currentIdea.st,
		currentIdea.dt,
		currentIdea.tt,
		currentIdea.ds
	);

	if (isNewIdea == true){ // si la idea es nova, ja esta insertada
		return true;
	}else{
		if (isNewIdea != null){
			if	(currentIdea.st != isNewIdea.st || 
				currentIdea.dt != isNewIdea.dt || 
				currentIdea.tt != isNewIdea.tt ||  
				currentIdea.ds != isNewIdea.ds){ // idea no identica, cal actualitzar
				return Db.updateIdea(currentIdea.id,currentIdea.st,currentIdea.dt,currentIdea.tt,currentIdea.ds);
				
			}else{// idea identica
				return false; 
			}
		}else{ // id de idea en blanc o nula 
			return false;
		}
	}
};

Db.insertIdeaOnce = function (id,status,data,titol,descripcio){
	if (id != null && id !=""){
		Ti.API.info('Db.insertIdeaOnce: consultant idea:'+id);
		
		var rows = Ti.App.db.execute('SELECT * FROM idea WHERE id = ?',id);
		if (rows.getRowCount() == 0){
			Db.insertIdea(id,status,data,titol,descripcio);
			
			return true;
		}else{
			Ti.API.info('Db.insertIdeaOnce: idea ja existia:'+id);
			var returnObject = {
				st: rows.fieldByName('status'),
				dt: rows.fieldByName('data'),
				tt: rows.fieldByName('titol'),
				ds: rows.fieldByName('descripcio')
			};
			
			return returnObject;
		}
		
	}else{
		Ti.API.info('Db.insertIdeaOnce: id es blank!!');
		return null;
	}
};

Db.insertIdea = function (id,status,data,titol,descripcio){
	Ti.API.info('Db.insertIdea:'+id+';'+status+';'+data+';'+titol);	
	Ti.App.db.execute("INSERT INTO idea(id,status,data,titol,descripcio,esNou,haCanviat,vist,ultimCanvi) values(?,?,?,?,?,1,0,0,strftime('%s','now'));",id,status,data,titol,descripcio);
	return Ti.App.db.rowsAffected == 1;
};

Db.updateIdea = function (id,status,data,titol,descripcio){
	Ti.API.info('Db.updateIdea:'+id+';'+status+';'+data+';'+titol);	
	Ti.App.db.execute("UPDATE idea SET status=? , data=? , titol=? , descripcio=? , esNou = 0 , haCanviat = 1, vist = 0,ultimCanvi = strftime('%s','now') WHERE id=? ;",status,data,titol,descripcio,id);
	return Ti.App.db.rowsAffected == 1;
};

Db.deleteIdea = function (id){
	Ti.API.info('Db.deleteIdea:'+id);
	Ti.App.db.execute("DELETE FROM idea WHERE id=? ;",id);
	return Ti.App.db.rowsAffected == 1;
};

Db.retrieveIdeasByStatus = function (statusId){
	
	var rows = Ti.App.db.execute("SELECT * FROM idea WHERE status=? order by data DESC ;",statusId);
	var returnRows = [];
	var retrieveIdea = null;
	var numRowsFound = rows.getRowCount();
	if (numRowsFound > 0){
		while( rows.isValidRow() ){
			
			retrieveIdea = {
					id: 	rows.fieldByName('id'),
					st: 	rows.fieldByName('status'),
					dt:		rows.fieldByName('data'),
					tt: 	rows.fieldByName('titol'),
					ds:		rows.fieldByName('descripcio'),
					nou:	rows.fieldByName('esNou'),
					canviat:rows.fieldByName('haCanviat'),
					uc: 	rows.fieldByName('ultimCanvi'),
					noVotats : this.countNotVotedSolutions(rows.fieldByName('id'))
			};
			returnRows.push(retrieveIdea);
			rows.next();
		}
		Ti.API.info('Db.retrieveIdeasByStatus:'+statusId+'; idees trobades: '+numRowsFound);
		
		return returnRows;
	}else{
		Ti.API.info('Db.retrieveIdeasByStatus:'+statusId+'; cap idea trobada');
		
		return false;
	}
};


Db.retrieveIdea = function (id){
	
	var rows = Ti.App.db.execute("SELECT * FROM idea WHERE id = ? ",id);
	if ( rows.isValidRow() && rows.getRowCount() == 1 ){
		var retrieveIdeaReturn = {
				id: 	rows.fieldByName('id'),
				st: 	rows.fieldByName('status'),
				dt:		rows.fieldByName('data'),
				tt: 	rows.fieldByName('titol'),
				ds: 	rows.fieldByName('descripcio'),
				nou:	rows.fieldByName('esNou'),
				canviat:rows.fieldByName('haCanviat'),
				uc:		rows.fieldByName('ultimCanvi')
		};
		Ti.API.info('Db.retrieveIdea:'+id+'; trobat:'+retrieveIdeaReturn.tt);
		
		return retrieveIdeaReturn;
	}else{
		Ti.API.info('Db.retrieveIdea:'+id+'; no trobat');
		
		return false;
	}
};

Db.markIdeaAsUpdated = function(id){
	Ti.App.db.execute("UPDATE idea SET esNou = 0 , haCanviat = 1, vist = 0, ultimCanvi = strftime('%s','now') WHERE id=? AND esNou = 0 and haCanviat = 0 ;",id);
	return Ti.App.db.rowsAffected == 1;
};

Db.markIdeaAsRead = function(id){
	Ti.App.db.execute("UPDATE idea SET esNou = 0 , haCanviat = 0, vist = 1, ultimCanvi = strftime('%s','now') WHERE id=? AND (esNou = 1 OR haCanviat = 1);",id);
	return Ti.App.db.rowsAffected == 1;
	
};

Db.clearIdeaStateForOldIdeas = function(){
	var now = Math.round(new Date().getTime() / 1000);
	var lastDay = now - (60 * 60 *24);
	Ti.App.db.execute("UPDATE idea SET esNou = 0 , haCanviat = 0 WHERE ultimCanvi < ? AND (esNou = 1 OR haCanviat = 1);",lastDay);
	//Ti.API.info('Db.clearIdeaStateForOldIdeas:afected ideas:' +Ti.App.db.rowsAffected);
	Ti.App.db.execute("UPDATE solucio SET esNou = 0, haCanviat = 0 WHERE ultimCanvi < ? AND (esNou = 1 OR haCanviat = 1);",lastDay);
	//Ti.API.info('Db.clearIdeaStateForOldIdeas:afected solucions:' +Ti.App.db.rowsAffected);	
};

Db.countNewOrUpdatedIdeas = function (){	
	var rows = Ti.App.db.execute("SELECT count(*) FROM idea WHERE esNou=1 OR haCanviat=1;");
	return rows.field(0);
};

Db.insertOrUpdateSolucio = function (currentSolucio){
	var isNewSolucio = Db.insertSolucioOnce(
		currentSolucio.sid,
		currentSolucio.id,
		currentSolucio.st,
		currentSolucio.tt,
		currentSolucio.ds,
		currentSolucio.vt,
		currentSolucio.sl
	);
	var nomesVots = false;
	if (isNewSolucio == true){ // si la solucio es nova, ja esta insertada
		return true;
	}else{
		if (isNewSolucio != null){
			if (currentSolucio.id != isNewSolucio.id || 
				currentSolucio.st != isNewSolucio.st || 
				currentSolucio.tt != isNewSolucio.tt ||  
				currentSolucio.ds != isNewSolucio.ds ||
				currentSolucio.vt != isNewSolucio.vt ||
				currentSolucio.sl != isNewSolucio.sl){ // idea no identica, cal actualitzar
				
				if(currentSolucio.id == isNewSolucio.id && 
						currentSolucio.st == isNewSolucio.st && 
						currentSolucio.tt == isNewSolucio.tt &&  
						currentSolucio.ds == isNewSolucio.ds &&
						currentSolucio.vt != isNewSolucio.vt &&
						currentSolucio.sl == isNewSolucio.sl){
					nomesVots = true;
				}else{
					nomesVots = false;
				}
				return Db.updateSolucio(nomesVots,currentSolucio.sid,currentSolucio.id,currentSolucio.st,currentSolucio.tt,currentSolucio.ds,currentSolucio.vt,currentSolucio.sl);
				
			}else{// solucio identica
				return false; 
			}
		}else{ // id de solucio en blanc o nula 
			return false;
		}
	}
};

Db.insertSolucioOnce = function (idSolucio,idIdea,status,titol,descripcio,vots,sl){
	if (idSolucio != null && idSolucio !=""){
		Ti.API.info('Db.insertSolucioOnce: consultant solucio:'+idSolucio);
		
		var rows = Ti.App.db.execute('SELECT * FROM solucio WHERE id = ?',idSolucio);
		if (rows.getRowCount() == 0){
			Db.insertSolucio(idSolucio,idIdea,status,titol,descripcio,vots,sl);
			
			return true;
		}else{
			Ti.API.info('Db.insertSolucioOnce: solucio ja existia:'+idSolucio);
			var returnObject = {
				id: rows.fieldByName('idIdea'),
				st: rows.fieldByName('status'),
				tt: rows.fieldByName('titol'),
				ds: rows.fieldByName('descripcio'),
				vt: rows.fieldByName('vots'),
				sl: rows.fieldByName('sl'),
				uc:	rows.fieldByName('ultimCanvi')
			};
			
			return returnObject;
		}
	}else{
		Ti.API.info('Db.insertSolucioOnce: id solucio es blank!!');
		return null;
	}
};

Db.insertSolucio = function (idSolucio,idIdea,status,titol,descripcio,vots,sl){
	Ti.API.info('Db.insertSolucio:'+idSolucio+';'+status+';'+titol);
	Db.markIdeaAsUpdated(idIdea);
	Ti.App.db.execute("INSERT INTO solucio(id,idIdea,status,titol,descripcio,vots,sl,esNou,haCanviat,ultimCanvi,votat) values(?,?,?,?,?,?,?,1,0,strftime('%s','now'),2);",idSolucio,idIdea,status,titol,descripcio,vots,sl);
	return Ti.App.db.rowsAffected == 1;
};

Db.updateSolucio = function (nomesVots,idSolucio,idIdea,status,titol,descripcio,vots,sl){
	Ti.API.info('Db.updateSolucio:'+idSolucio+';'+idIdea+';'+status+';'+titol);
	if(nomesVots == false){ // si nomes voten una solucio, no cal que surti la idea com actualitzada.
		Db.markIdeaAsUpdated(idIdea);
	}
	Ti.App.db.execute("UPDATE solucio SET idIdea=? , status=? , titol=? , descripcio=? , vots=? , sl=? , esNou = 0 , haCanviat = 1, ultimCanvi = strftime('%s','now') WHERE id=? ;",idIdea,status,titol,descripcio,vots,sl,idSolucio);
	return Ti.App.db.rowsAffected == 1;
};

Db.deleteSolucio = function (id){
	Ti.API.info('Db.deleteSolucio:'+id);
	Ti.App.db.execute("DELETE FROM solucio WHERE id=? ;",id);
	return Ti.App.db.rowsAffected == 1;
};

Db.retrieveSolucio = function (id){
	var rows = Ti.App.db.execute("SELECT * FROM solucio WHERE id = ? ",id);
	if ( rows.isValidRow() && rows.getRowCount() == 1 ){
		var retrieveSolucioReturn = {
				sid: 	rows.fieldByName('id'),
				id: 	rows.fieldByName('idIdea'),
				st: 	rows.fieldByName('status'),
				tt: 	rows.fieldByName('titol'),
				ds:		rows.fieldByName('descripcio'),
				vt:		rows.fieldByName('vots'),
				sl:		rows.fieldByName('sl'),
				nou:	rows.fieldByName('esNou'),
				canviat:rows.fieldByName('haCanviat'),
				uc:		rows.fieldByName('ultimCanvi'),
				votat:	rows.fieldByName('votat')
		};
		Ti.API.info('Db.retrieveSolucio:'+id+'; trobat:'+retrieveSolucioReturn.tt);
		return retrieveSolucioReturn;
	}else{
		Ti.API.info('Db.retrieveSolucio:'+id+'; no trobat');
		return false;
	}
};

Db.sincronitzarVotSolucio = function (idSolucio,vot){
	Ti.API.info('Db.sincronitzarVotSolucio:'+idSolucio+" "+vot);
	Ti.App.db.execute("UPDATE solucio SET votat=? WHERE id=? ;",vot +"",idSolucio+"");
	return Ti.App.db.rowsAffected == 1;
};

Db.votarSolucio = function (idSolucio,vot){
	Ti.API.info('Db.votarSolucio:'+idSolucio+" "+vot);
	var currSolucio = Db.retrieveSolucio(idSolucio);
	var newVot = null;
	Ti.API.info('vots abans:'+currSolucio.vt);
	if (currSolucio.votat == 2){
		newVot = currSolucio.vt + vot;
	}else{
		newVot = (currSolucio.vt - currSolucio.votat) + vot;
	}
	Ti.API.info('vots despres:'+newVot);
	Ti.App.db.execute("UPDATE solucio SET votat=? ,vots = ? WHERE id=? ;",vot +"",newVot+"",idSolucio+"");
	return newVot;
};

Db.countNotVotedSolutions = function (idIdea){
	var rows = Ti.App.db.execute("SELECT votat FROM solucio WHERE idIdea=? and votat=2;",idIdea);
	return rows.getRowCount();
};

Db.retrieveSolucionsByIdeaId = function (idIdea){
	var rows = Ti.App.db.execute("SELECT * FROM solucio WHERE idIdea=? order by id ASC ;",idIdea);
	var returnRows = [];
	var retrieveSolucio = null;
	var numRowsFound = rows.getRowCount();
	if (numRowsFound > 0){
		while( rows.isValidRow() ){
			retrieveSolucio = {
					sid: 	rows.fieldByName('id'),
					id: 	rows.fieldByName('idIdea'),
					st: 	rows.fieldByName('status'),
					tt: 	rows.fieldByName('titol'),
					vt:		rows.fieldByName('vots'),
					nou:	rows.fieldByName('esNou'),
					canviat:rows.fieldByName('haCanviat'),
					uc:		rows.fieldByName('ultimCanvi'),
					votat:	rows.fieldByName('votat')
			};
			returnRows.push(retrieveSolucio);
			rows.next();
		}
		Ti.API.info('Db.retrieveSolucionsByIdeaId:'+idIdea+'; trobats:'+numRowsFound);
		return returnRows;
	}else{
		Ti.API.info('Db.retrieveSolucionsByIdeaId:'+idIdea+'; no trobat');
		return [];
	}
};

Db.countNewOrUpdatedSolucions = function (){
	var rows = Ti.App.db.execute("SELECT count(*) FROM solucio WHERE esNou=1 OR haCanviat=1;");
	return rows.field(0);
};

Db.resetDb = function(){
	Ti.API.info('Db.resetDb: borrant taules');
	Ti.App.db.execute("DROP TABLE IF EXISTS idea;");
	Ti.App.db.execute("DROP TABLE IF EXISTS solucio;");
	Ti.App.db.execute("DROP TABLE IF EXISTS comentari;");
	Db.setVersion(false);
	Db.currentVersion = false;
};

Db.truncate = function (){
	Ti.API.info('Db.truncate: borrant registres antics');
	Ti.App.db.execute("DELETE FROM idea WHERE 1;");
	Ti.App.db.execute("DELETE FROM solucio WHERE 1;");
	Ti.App.db.execute("DELETE FROM comentari WHERE 1;");
};

Db.getVersion = function(){return Ti.App.Properties.getString('dbVersion',false);};

Db.setVersion = function(version){
	Db.currentVersion = version;
	Ti.App.Properties.setString('dbVersion',Db.currentVersion);
};